{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import os, sys\n",
    "sys.path.insert(0, os.path.realpath('..'))\n",
    "from firecares.firestation.models import FireDepartment\n",
    "import csv\n",
    "import re\n",
    "import django\n",
    "import pandas as pd\n",
    "django.setup()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# See s3://firecares-share/2016-12 Metro Roster.csv\n",
    "\n",
    "df = pd.read_csv('../2016-12 Metro Roster.csv')\n",
    "df = df[pd.notnull(df['Address']) & pd.isnull(df['Country'])]\n",
    "df = df[['Department', 'Address', 'Email', 'First Name', 'Last Name', 'State']]\n",
    "df['Address'] = df['Address'].apply(lambda x: re.sub('Ste?\\s+.*|\\#.*', '', x).strip())\n",
    "df['Department'] = df['Department'].apply(lambda x: re.sub('Dept|\\s{2+}', '', x).strip())\n",
    "rows = df.to_dict(orient='records')\n",
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print map(lambda x: (x.id, x.name, x.headquarters_address.address_line1), FireDepartment.objects.filter(headquarters_address__address_line1__icontains='210 W San Jacinto Ave', state='CA'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print map(lambda x: (x.id, x.name, x.headquarters_address.address_line1), FireDepartment.objects.filter(name__icontains='Richmond Fire', state='VA'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "counts = {'multiple': 0, 'found': 0, 'nomatches': 0}\n",
    "ids = []\n",
    "for r in rows:\n",
    "    fd = FireDepartment.objects.filter(headquarters_address__address_line1__icontains=r['Address'], state=r['State'])\n",
    "    if len(fd) > 1:\n",
    "        print 'MISMATCH POTENTIAL: {} - ({})'.format(r['Department'], r['State'])\n",
    "        counts['multiple'] = counts['multiple'] + 1\n",
    "        ids.append(0)\n",
    "    elif len(fd) == 1:\n",
    "        print 'FOUND: {}'.format(fd.first().name)\n",
    "        counts['found'] = counts['found'] + 1\n",
    "        ids.append(fd.first().id)\n",
    "    else:\n",
    "        # Fall back to finding by name\n",
    "        fd = FireDepartment.objects.filter(name__icontains=r['Department'], state=r['State'])\n",
    "        if fd:\n",
    "            print 'FOUND (by name): {}'.format(fd.first().name)\n",
    "            counts['found'] = counts['found'] + 1\n",
    "            ids.append(fd.first().id)\n",
    "        else:\n",
    "            print 'NO MATCHES: {} - ({})'.format(r['Department'], r['State'])\n",
    "            counts['nomatches'] = counts['nomatches'] + 1\n",
    "            ids.append(0)\n",
    "        \n",
    "print counts"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Django Shell-Plus",
   "language": "python",
   "name": "django_extensions"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
